var express = require('express');
const { connection } = require('../model/db') // 获取连接实例
const router = express.Router()

// 示例
// router.get("/msglist", (req, res) => {
//     connection.query(`select * from user;`, (err, data) => {
//         if (err) {
//             console.log("err", err)
//             res.send('query error')
//         } else {
//             // 将 MySQL 查询结果作为路由返回值
//             res.send({
//                 code: 200,
//                 msg: "获取数据成功",
//                 data
//             })
//         }
//     })
// })


// 根据当前登录用户的角色id去查询自己拥有的权限接口

// 获取角色列表数据接口
router.get("/rolelist", (req, res) => {
  connection.query(`select * from role;`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data,
      });
    }
  });
});

// 添加角色接口
router.post("/addrole", (req, res) => {
  let obj = req.body

  let sql1 = `select * from role where role_name='${obj.role_name}';`
  connection.query(`${sql1}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      if (data.length !== 0) {
        res.send({
          code: 400,
          msg: "已有该角色"
        })
      } else {
        let sql = `insert into role(role_name,mark) values('${obj.role_name}','${obj.mark}');`
        connection.query(`${sql}`, (err, data) => {
          if (err) {
            console.log("err", err);
            res.send({
              code: 400,
              msg: "query error",
              err
            });
          } else {
            res.send({
              code: 200,
              msg: "添加角色成功"
            })
          }
        });
      }
    }
  });
})

// 数据回填接口
router.get("/getrole", (req, res) => {
  let id = req.query.id
  connection.query(`select * from role where id='${id}';`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        role: data[0],
      });
    }
  });
});

// 修改角色接口
router.post("/editrole", async (req, res) => {
  let body = req.body
  let sql = `update role set role_name='${body.role_name}',mark='${body.mark}' where id='${body.id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "修改角色成功",
      });
    }
  });
})

// 删除角色接口
router.get("/delrole", (req, res) => {
  let id = req.query.id
  let sql = `delete from role where id='${id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "删除角色成功"
      })
    }
  });
});


// 根据试卷id查询学院id接口
router.get("/getdept", (req, res) => {
  let paper_id = req.query.paperId
  console.log(`paper_id`, paper_id);
  connection.query(`select d.*,q.write_name,t.dept_name from dept_paper d join question_bank q join department as t on d.write_id=q.id and d.dept_id=t.id where d.id=${paper_id};`,
    (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      console.log(`data`, data);
      if (data.length == 0) {
        res.send({
          code: 400,
          msg: "没有查到当前试卷的学院"
        });
      } else {
        res.send({
          code: 200,
          msg: "获取数据成功",
          dept_id: data[0].dept_id,
          obj: data[0]
        });
      }

    }
  });
})

// 根据出题方id去查询答题方题库中的题接口
router.get("/getquestions", (req, res) => {
  let select_id = req.query.selectId
  let sql = `select q.*,d.dept_name from select_rule s join question_bank q join department d on s.answer_id=q.dept_id and q.dept_id=d.id where s.select_id='${select_id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data
      });
    }
  });
})

// 预览试卷接口  根据出题方id去查询出的试卷
router.get("/getpapers", (req, res) => {
  let select_id = req.query.selectId
  let sql = `select p.*,d.dept_name,t.teacher_name,qb.write_name from select_rule s join dept_paper p join department d join teacher t join question_bank qb on s.answer_id=p.dept_id and p.dept_id=d.id and p.teacher_id=t.id and p.write_id=qb.id where s.select_id='${select_id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data
      });
    }
  });
})

// 根据学院和班级去查询试卷接口(用于生成成绩单)
router.get("/getclasspapers",(req,res)=>{
  let dept_id = req.query.deptId
  let classname = req.query.classname
  let sql = `select * from student_paper`
  if(dept_id && classname){
      sql =  `select sp.*,s.stu_name,s.classroom,d.dept_name from student_paper sp join student s join department d on sp.stu_id=s.id and s.college_id=d.id where s.college_id='${dept_id}' and s.classname='${classname}';`
  }
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data
      });
    }
  });

})

// 根据试卷id和学生id查询有没有答案接口
router.get("/getanswer", (req, res) => {
  let paper_id = req.query.paperId
  let stu_id = req.query.stuId
  let sql = `select * from student_paper where paper_id='${paper_id}' and stu_id='${stu_id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data
      });
    }
  });
})

// 根据试卷id修改试卷接口
router.post("/editpaper", (req, res) => {
  let body = req.body
  let sql = `update dept_paper set paper_name='${body.paper_name}',write_id='${body.write_id}',begin_time='${body.begin_time}',end_time='${body.end_time}' where id='${body.id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "修改数据成功"
      });
    }
  });
})

// 根据试卷id删除试卷接口
router.get("/delpaper", (req, res) => {
  let id = req.query.id
  let sql = `delete from dept_paper where id='${id}';`
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send({
        code: 400,
        msg: "query error",
        err
      });
    } else {
      res.send({
        code: 200,
        msg: "删除数据成功"
      });
    }
  });
})



// 读取面试题数据接口
router.get("/questionlist", (req, res) => {
  let stackId = req.query.stackId
  let timeSort = req.query.timeSort
  let sql = 'select i.*,f.id author_id,f.nickname,f.avater,t.stack_name from interviewQuestion i join frontUser f join technologyStack t on i.uid_id = f.id and i.stack_id = t.id where question_state="已通过";'
  if (stackId) {
    sql = `select i.*,f.id author_id,f.nickname,f.avater,t.stack_name from interviewQuestion i join frontUser f join technologyStack t on i.uid_id = f.id and i.stack_id = t.id where question_state="已通过" and stack_id=${stackId};`
  }
  if (timeSort) {
    sql = `select i.*,f.id author_id,f.nickname,f.avater,t.stack_name from interviewQuestion i join frontUser f join technologyStack t on i.uid_id = f.id and i.stack_id = t.id where question_state="已通过" order by question_time desc;`
  }
  if (stackId && timeSort) {
    sql = `select i.*,f.id author_id,f.nickname,f.avater,t.stack_name from interviewQuestion i join frontUser f join technologyStack t on i.uid_id = f.id and i.stack_id = t.id where question_state="已通过" and stack_id=${stackId} order by question_time desc;`
  }
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send("query error");
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data,
      });
    }
  });
});

// 获取面试题评论列表数据接口
router.get("/commentlist", (req, res) => {
  let questionId = req.query.questionId
  let sql = 'select c.*,f.avater,f.nickname from comment c join frontUser f on c.uid_id=f.id;'
  if (questionId) {
    sql = `select c.*,f.avater,f.nickname from comment c join frontUser f on c.uid_id=f.id where question_id=${questionId};`
  }
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send("query error");
    } else {
      res.send({
        code: 200,
        msg: "获取数据成功",
        data,
      });
    }
  });
});

// 获取面试题评论表树形结构接口
router.get("/commentTreelist", (req, res) => {
  let questionId = req.query.questionId
  let timeSort = "desc"
  let sql = 'select c.*,f.avater,f.nickname from comment c join frontUser f on c.uid_id=f.id;'
  if (questionId) {
    sql = `select c.*,f.avater,f.nickname from comment c join frontUser f on c.uid_id=f.id where question_id=${questionId};`
  }
  if (questionId && timeSort) {
    sql = `select c.*,f.avater,f.nickname from comment c join frontUser f on c.uid_id=f.id where question_id=${questionId} order by ctime desc;`
  }
  connection.query(`${sql}`, (err, data) => {
    if (err) {
      console.log("err", err);
      res.send("query error");
    } else {
      let list = []
      let commentObj = {}
      data.forEach(item => {
        commentObj[item.id] = item
      })
      data.forEach((item) => {
        if (!item['pid_id']) {
          list.push(item);
        } else {
          // 有父级
          if (!commentObj[item.pid_id]["children"]) {
            commentObj[item.pid_id]["children"] = [];
          }
          commentObj[item.pid_id]["children"].push(item);
        }
      });
      res.send({
        code: 200,
        msg: "获取数据成功",
        list,
      });
    }
  });
});

module.exports = router;